package com.hiv.mapper;

import com.hiv.common.param.InspectionStatisticsParam;
import com.hiv.common.param.page.PageQuerySamplingRecordParam;
import com.hiv.mapper.domain.SamplingRecord;

import java.util.Date;
import java.util.List;
import java.util.Set;

import com.hiv.mapper.domain.SamplingRecordResult;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 采样数据Mapper接口
 *
 * @author zhanghong
 * @date 2022-09-20
 */
@Mapper
@Repository
public interface SamplingRecordMapper {
    SamplingRecord selectById(Long id);

    List<SamplingRecord> selectList(SamplingRecord samplingRecord);

    int insert(SamplingRecord samplingRecord);

    int batchInsert(List<SamplingRecord> samplingRecordList);

    int update(SamplingRecord samplingRecord);

    int deleteById(Long id);

    int deleteByIds(String[] ids);

    @Select({"<script>",
            "   select count(1) from sampling_record",
            "   <where>",
            "       deleted=0",
            "       <if test=\"param.antiviralCode!=null and param.antiviralCode!=''\">and antiviral_code like concat('%',#{param.antiviralCode},'%')</if>",
            "       <if test=\"param.barCode!=null and param.barCode!=''\">and bar_code like concat('%',#{param.barCode},'%')</if>",
            "       <if test=\"param.inspectionId!=null\">and inspection_id=#{param.inspectionId}</if>",
            "       <if test=\"param.hospitalId!=null\">and hospital_id=#{param.hospitalId}</if>",
            "       <if test=\"param.hospitalIdList != null and param.hospitalIdList.size() != 0\">",
            "           and hospital_id in",
            "           <foreach collection=\"param.hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "       </if> ",
            "       <if test=\"param.samplerId!=null\">and sampler_id=#{param.samplerId}</if>",
            "       <if test=\"param.checkType!=null\">and check_type=#{param.checkType}</if>",
            "       <if test=\"param.sampleStatus!=null\">and sample_status=#{param.sampleStatus}</if>",
            "       <if test=\"param.antiviralCodeOrBarCode!=null and param.antiviralCodeOrBarCode!=''\">",
            "           and (antiviral_code like concat('%',#{param.antiviralCodeOrBarCode},'%') or bar_code like concat('%',#{param.antiviralCodeOrBarCode},'%'))",
            "       </if>",
            "       <if test=\"param.startTime!=null \">and create_time&gt;#{param.startTime}</if>",
            "       <if test=\"param.endTime!=null \">and create_time&lt;#{param.endTime}</if>",
            "       <if test=\"param.inspectionSamplingStatus!=null\">and inspection_sampling_status=#{param.inspectionSamplingStatus}</if>",
            "       <if test=\"param.samplingStatus!=null\">and sampling_status=#{param.samplingStatus}</if>",
            "       <if test=\"param.queryException==0\">",
            "           <if test=\"param.sampleStatusList != null and param.sampleStatusList.size() != 0\">",
            "               and sample_status in",
            "               <foreach collection=\"param.sampleStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "                   #{item} ",
            "               </foreach> ",
            "           </if> ",
            "           <if test=\"param.inspectionSamplingStatus!=null\">and inspection_sampling_status=#{param.inspectionSamplingStatus}</if>",
            "       </if>",
            "       <if test=\"param.queryException==1\">",
            "           and (sample_status in (2,4) or inspection_sampling_status=2)",
            "       </if>",
            "       <if test=\"param.completeStatus!=null\">and complete_status=#{param.completeStatus}</if>",
            "       <if test=\"param.auditStatus!=null\">and audit_status=#{param.auditStatus}</if>",
            "       <if test=\"param.auditStatusList != null and param.auditStatusList.size() != 0\">",
            "           and audit_status in",
            "           <foreach collection=\"param.auditStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "       </if>",
            "       <if test=\"param.ratifyStatus!=null\">and ratify_status=#{param.ratifyStatus}</if>",
            "       <if test=\"param.ratifyStatusList != null and param.ratifyStatusList.size() != 0\">",
            "           and ratify_status in",
            "           <foreach collection=\"param.ratifyStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "       </if>",
            "       <if test=\"param.reportStatus!=null\">and report_status=#{param.reportStatus}</if>",
            "   </where>",
            "</script>"})
    long pageCount(@Param("param") PageQuerySamplingRecordParam param);

    @Select({"<script>",
            "   select * from sampling_record",
            "   <where>",
            "       deleted=0",
            "       <if test=\"param.antiviralCode!=null and param.antiviralCode!=''\">and antiviral_code like concat('%',#{param.antiviralCode},'%')</if>",
            "       <if test=\"param.barCode!=null and param.barCode!=''\">and bar_code like concat('%',#{param.barCode},'%')</if>",
            "       <if test=\"param.inspectionId!=null\">and inspection_id=#{param.inspectionId}</if>",
            "       <if test=\"param.hospitalId!=null\">and hospital_id=#{param.hospitalId}</if>",
            "       <if test=\"param.hospitalIdList != null and param.hospitalIdList.size() != 0\">",
            "           and hospital_id in",
            "           <foreach collection=\"param.hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "       </if> ",
            "       <if test=\"param.inspectionSamplingStatus!=null\">and inspection_sampling_status=#{param.inspectionSamplingStatus}</if>",
            "       <if test=\"param.samplerId!=null\">and sampler_id=#{param.samplerId}</if>",
            "       <if test=\"param.checkType!=null\">and check_type=#{param.checkType}</if>",
            "       <if test=\"param.sampleStatus!=null\">and sample_status=#{param.sampleStatus}</if>",
            "       <if test=\"param.antiviralCodeOrBarCode!=null and param.antiviralCodeOrBarCode!=''\">",
            "           and (antiviral_code like concat('%',#{param.antiviralCodeOrBarCode},'%') or bar_code like concat('%',#{param.antiviralCodeOrBarCode},'%'))",
            "       </if>",
            "       <if test=\"param.startTime!=null \">and create_time&gt;#{param.startTime}</if>",
            "       <if test=\"param.endTime!=null \">and create_time&lt;#{param.endTime}</if>",
            "       <if test=\"param.inspectionSamplingStatus!=null\">and inspection_sampling_status=#{param.inspectionSamplingStatus}</if>",
            "       <if test=\"param.samplingStatus!=null\">and sampling_status=#{param.samplingStatus}</if>",
            "       <if test=\"param.queryException==0\">",
            "           <if test=\"param.sampleStatusList != null and param.sampleStatusList.size() != 0\">",
            "               and sample_status in",
            "               <foreach collection=\"param.sampleStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "                   #{item} ",
            "               </foreach> ",
            "           </if> ",
            "           <if test=\"param.inspectionSamplingStatus!=null\">and inspection_sampling_status=#{param.inspectionSamplingStatus}</if>",
            "       </if>",
            "       <if test=\"param.queryException==1\">",
            "           and (sample_status in (2,4) or inspection_sampling_status=2)",
            "       </if>",
            "       <if test=\"param.completeStatus!=null\">and complete_status=#{param.completeStatus}</if>",
            "       <if test=\"param.auditStatus!=null\">and audit_status=#{param.auditStatus}</if>",
            "       <if test=\"param.auditStatusList != null and param.auditStatusList.size() != 0\">",
            "           and audit_status in",
            "           <foreach collection=\"param.auditStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "       </if>",
            "       <if test=\"param.ratifyStatus!=null\">and ratify_status=#{param.ratifyStatus}</if>",
            "       <if test=\"param.ratifyStatusList != null and param.ratifyStatusList.size() != 0\">",
            "           and ratify_status in",
            "           <foreach collection=\"param.ratifyStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "       </if>",
            "       <if test=\"param.reportStatus!=null\">and report_status=#{param.reportStatus}</if>",
            "   </where>",
            "   order by create_time desc limit #{limit} offset #{offset}",
            "</script>"})
    List<SamplingRecord> pageQuery(@Param("param") PageQuerySamplingRecordParam param, @Param("limit") int limit, @Param("offset") long offset);

    @Select({"<script>",
            "   select count(1) from sampling_record sr",
            "   inner join sampling_box_record sbr on sbr.sampling_id = sr.id",
            "   inner join sampling_box sb on sb.id = sbr.box_id",
            "   inner join batch b on b.id = sb.batch_id",
            "   <where>",
            "       sr.deleted=0",
            "       <if test=\"param.hospitalId!=null\">and sr.hospital_id=#{param.hospitalId}</if>",
            "       <if test=\"param.inspectionId!=null\">and sr.inspection_id=#{param.inspectionId}</if>",
            "       <if test=\"param.sampleStatusList != null and param.sampleStatusList.size() != 0\">",
            "           and (sr.sample_status in",
            "           <foreach collection=\"param.sampleStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "           or sr.report_status=2)",
            "       </if> ",
            "       <if test=\"param.expressOrBatchCode!=null and param.expressOrBatchCode!=''\">",
            "           and (b.express like concat('%',#{param.expressOrBatchCode},'%') or b.code like concat('%',#{param.expressOrBatchCode},'%'))",
            "       </if>",
            "       <if test=\"param.antiviralCode!=null and param.antiviralCode!=''\">and antiviral_code like concat('%',#{param.antiviralCode},'%')</if>",
            "   </where>",
            "</script>"})
    long forDoctorPageCount(@Param("param") PageQuerySamplingRecordParam param);

    @Select({"<script>",
            "   select sr.* from sampling_record sr",
            "   inner join sampling_box_record sbr on sbr.sampling_id = sr.id",
            "   inner join sampling_box sb on sb.id = sbr.box_id",
            "   inner join batch b on b.id = sb.batch_id",
            "   <where>",
            "       sr.deleted=0",
            "       <if test=\"param.hospitalId!=null\">and sr.hospital_id=#{param.hospitalId}</if>",
            "       <if test=\"param.inspectionId!=null\">and sr.inspection_id=#{param.inspectionId}</if>",
            "       <if test=\"param.sampleStatusList != null and param.sampleStatusList.size() != 0\">",
            "           and (sr.sample_status in",
            "           <foreach collection=\"param.sampleStatusList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "               #{item} ",
            "           </foreach> ",
            "           or sr.report_status=2)",
            "       </if> ",
            "       <if test=\"param.expressOrBatchCode!=null and param.expressOrBatchCode!=''\">",
            "           and (b.express like concat('%',#{param.expressOrBatchCode},'%') or b.code like concat('%',#{param.expressOrBatchCode},'%'))",
            "       </if>",
            "       <if test=\"param.antiviralCode!=null and param.antiviralCode!=''\">and antiviral_code like concat('%',#{param.antiviralCode},'%')</if>",
            "   </where>",
            "</script>"})
    List<SamplingRecord> forDoctorPageQuery(@Param("param") PageQuerySamplingRecordParam param, @Param("limit") int limit, @Param("offset") long offset);

    @Select({"<script>",
            "   select count(1) from sampling_record record",
            "   inner join sampling_box_record sbr on record.id=sbr.sampling_id",
            "   inner join sampling_box box on box.id=sbr.box_id",
            "   <where>",
            "       record.deleted=0",
            "       <if test=\"param.expressCodeOrBoxCode!=null and param.expressCodeOrBoxCode!=''\">",
            "           and (box.express like concat('%',#{param.expressCodeOrBoxCode},'%') or (box.box_code like concat('%',#{param.expressCodeOrBoxCode},'%'))",
            "       </if>",
            "   </where>",
            "</script>"})
    long unionPageCount(@Param("param") PageQuerySamplingRecordParam param);

    @Select({"<script>",
            "   select record.* from sampling_record record",
            "   inner join sampling_box_record sbr on record.id=sbr.sampling_id",
            "   inner join sampling_box box on box.id=sbr.box_id",
            "   <where>",
            "       record.deleted=0",
            "       <if test=\"param.expressCodeOrBoxCode!=null and param.expressCodeOrBoxCode!=''\">",
            "           and (box.express like concat('%',#{param.expressCodeOrBoxCode},'%') or (box.box_code like concat('%',#{param.expressCodeOrBoxCode},'%'))",
            "       </if>",
            "   </where>",
            "   order by record.create_time desc limit #{limit} offset #{offset}",
            "</script>"})
    List<SamplingRecord> unionPageQuery(@Param("param") PageQuerySamplingRecordParam param, @Param("limit") int limit, @Param("offset") long offset);

    @Select("select * from sampling_record where bar_code=#{barCode} limit 1")
    SamplingRecord queryByBarCode(String barCode);

    @Select({"<script>",
            "select sr.* from sampling_record sr ",
            "inner join sampling_box_record sbr on sbr.sampling_id=sr.id",
            "where sbr.box_id=#{boxId}",
            "</script>"})
    List<SamplingRecord> queryByBoxId(Long boxId);

    @Select({"<script>",
            "select count(1) from sampling_record sr ",
            "inner join sampling_box_record sbr on sbr.sampling_id=sr.id",
            "inner join sampling_box sb on sb.id=sbr.box_id",
            "where sb.batch_id=#{batchId}",
            "</script>"})
    Integer countSamplingNum(Long batchId);

    /**
     * 检验所-查询今日入库
     */
    //@Select("select count(1) from sampling_record where sample_status in (1,2,3,4) and to_days(storage_time) = to_days(now()) and inspection_id=#{inspectionId}")
    @Select({"<script>",
            "   select count(1) from sampling_record ",
            "   <where>",
            "      sample_status in (1,2,3,4,5,6,7) and to_days(storage_time) = to_days(now())",
            "      <if test=\"inspectionId != null\">and inspection_id=#{inspectionId}</if>",
            "   </where>",
            "</script>"})
    Integer inspectionTodayStorageCount(@Param("inspectionId") Long inspectionId);

    /**
     * 检验所-查询今日异常
     */
    @Select({"<script>",
            "   select count(1) from sampling_record ",
            "   <where>",
            "      sample_status in (2,4) and to_days(storage_time) = to_days(now())",
            "      <if test=\"inspectionId != null\">and inspection_id=#{inspectionId}</if>",
            "   </where>",
            "</script>"})
    Integer inspectionTodayExceptionCount(@Param("inspectionId") Long inspectionId);

    /**
     * 查询累计入库
     */
    @Select({"<script>",
            "   select count(1) from sampling_record ",
            "   <where>",
            "      sample_status in (1,2,3,4,5,6,7)",
            "      <if test=\"inspectionId != null\">and inspection_id=#{inspectionId}</if>",
            "   </where>",
            "</script>"})
    Integer inspectionTotalStorageCount(@Param("inspectionId") Long hospitalId);

    /**
     * 检验所-查询今日完成
     */
    @Select({"<script>",
            "   select count(1) from sampling_record",
            "   <where>",
            "      sample_status=6 and ratify_status=2 and  to_days(finish_time) = to_days(now())",
            "      <if test=\"param.inspectionId != null\">and inspection_id=#{param.inspectionId}</if>",
            "      <if test=\"param.hospitalId != null\">and hospital_id=#{param.hospitalId}</if>",
            "      <if test=\"param.hospitalIdList != null and param.hospitalIdList.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"param.hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    Integer inspectionTodayFinishCount(@Param("param") InspectionStatisticsParam param);

    /**
     * 检验所-查询检测中
     */
    @Select({"<script>",
            "   select count(1) from sampling_record",
            "   <where>",
            "      (sample_status=6 or sample_status=7) and report_status=1",
            "      <if test=\"param.inspectionId != null\">and inspection_id=#{param.inspectionId}</if>",
            "      <if test=\"param.hospitalId != null\">and hospital_id=#{param.hospitalId}</if>",
            "      <if test=\"param.hospitalIdList != null and param.hospitalIdList.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"param.hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    Integer inspectionCount(@Param("param") InspectionStatisticsParam param);

    /**
     * 查询累计完成
     */
    @Select({"<script>",
            "   select count(1) from sampling_record",
            "   <where>",
            "      sample_status in (5, 6, 7)",
            "      <if test=\"param.inspectionId != null\">and inspection_id=#{param.inspectionId}</if>",
            "      <if test=\"param.hospitalId != null\">and hospital_id=#{param.hospitalId}</if>",
            "      <if test=\"param.hospitalIdList != null and param.hospitalIdList.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"param.hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    Integer inspectionTotalFinishCount(@Param("param") InspectionStatisticsParam param);

    /**
     * 采样员：统计当天采集的数量
     */
    @Select("select count(1) from sampling_record where sampler_id=#{samplingUserId} and to_days(create_time) = to_days(now())")
    Integer todaySamplingNumCount(Long samplingUserId);

    /**
     * 采样员：统计样品异常数量[所有]
     */
    @Select("select count(1) from sampling_record where (sample_status in (2,4) or inspection_sampling_status=2) and sampler_id=#{samplingUserId}")
    Integer samplingExceptionCount(Long samplingUserId);

    /**
     * 采样员：统计样品异常数量[未重采]
     */
    @Select("select count(1) from sampling_record where resampling_status=0 and (sample_status in (2,4) or inspection_sampling_status=2) and sampler_id=#{samplingUserId}")
    Integer samplingUnResamplingExceptionCount(Long samplingUserId);

    /**
     * 采样员：统计待送检数量
     */
    @Select("select count(1) from sampling_record where sample_status=0 and sampler_id=#{samplingUserId}")
    Integer waitForInspectionNumCount(Long samplingUserId);

    /**
     * 医生：统计医院维度待检测数量
     */
    @Select("select count(1) from sampling_record where sample_status=5 and hospital_id=#{hospitalId}")
    Integer waitForInspectionNumCountForDoctor(Long hospitalId);

    /**
     * 医生：统计医院维度检测中数量
     */
    @Select("select count(1) from sampling_record where (sample_status = 6 or sample_status=7) and report_status=1 and inspection_sampling_status=1 and hospital_id=#{hospitalId}")
    Integer inspectionInNumCountForDocker(Long hospitalId);

    /**
     * 医生：统计医院维度已完成数量
     */
    @Select("select count(1) from sampling_record where report_status=2 and hospital_id=#{hospitalId}")
    Integer finishNumCountForDocker(Long hospitalId);

    /**
     * 患者：统计患者检测总数量
     */
    @Select("select * from sampling_record where antiviral_code=#{antiviralCode}")
    List<SamplingRecord> inspectionListByAntiviralCode(String antiviralCode);

    @Select("select count(1) from sampling_record where sample_status=6 and finish_time >= #{startTime} and finish_time <= #{endTime}")
    Integer inspectionFinishNumForAdmin(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select({"<script>",
            "   select id from sampling_record",
            "   <where>",
            "      deleted=0 and sample_status=6 and finish_time &gt;= #{startTime} and finish_time &lt;= #{endTime}",
            "      <if test=\"hospitalIdsSet != null and hospitalIdsSet.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"hospitalIdsSet\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    List<Long> inspectionFinishNumForHospitalIdList(@Param("hospitalIdsSet") Set<Long> hospitalIdsSet, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select("select count(distinct(antiviral_code)) from sampling_record where deleted=0 and create_time >= #{startTime} and create_time <= #{endTime}")
    Integer samplingCountByRange(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select("select count(1) from sampling_record where deleted=0 and sample_status in (5, 6, 7) and create_time >= #{startTime} and create_time <= #{endTime}")
    Integer inspectionCountByRange(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select("select count(1) from sampling_record where deleted=0 and sample_status=6 and create_time >= #{startTime} and create_time <= #{endTime}")
    Integer samplingFinishCountByRange(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select({"<script>",
            "   select count(1) from sampling_record",
            "   <where>",
            "      deleted=0 and create_time",
            "      and create_time &gt;= #{startTime} and create_time &lt;= #{endTime}",
            "      <if test=\"hospitalIdsSet != null and hospitalIdsSet.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"hospitalIdsSet\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    Integer samplingCountByRangeAndHospitalIdList(@Param("hospitalIdsSet") Set<Long> hospitalIdsSet, @Param("startTime") Date startTime, @Param("endTime") Date endTime);
    @Select({"<script>",
            "   select count(distinct(antiviral_code)) from sampling_record",
            "   <where>",
            "      deleted=0 and create_time and sample_status in (5, 6, 7)",
            "      and create_time &gt;= #{startTime} and create_time &lt;= #{endTime}",
            "      <if test=\"hospitalIdsSet != null and hospitalIdsSet.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"hospitalIdsSet\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    Integer inspectionCountByRangeAndHospitalIdList(@Param("hospitalIdsSet") Set<Long> hospitalIdsSet, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select({"<script>",
            "   select count(1) from sampling_record",
            "   <where>",
            "      deleted=0 and sample_status=6 and create_time &gt;= #{startTime} and create_time &lt;= #{endTime}",
            "      <if test=\"hospitalIdsSet != null and hospitalIdsSet.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"hospitalIdsSet\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "   </where>",
            "</script>"})
    Integer samplingFinishCountByRangeAndHospitalIdList(@Param("hospitalIdsSet") Set<Long> hospitalIdsSet, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    @Select("select * from sampling_record where inspection_id=#{inspectionId} and bar_code=#{barCode} limit 1")
    SamplingRecord getOne(@Param("inspectionId") Long inspectionId, @Param("barCode") String barCode);

    @Select({"<script>",
            "   select id,check_type from sampling_record",
            "   <where>",
            "      deleted=0 and finish_time is not null",
            "      <if test=\"inspectionId!=null\">",
            "          and inspection_id=#{inspectionId}",
            "      </if> ",
            "      <if test=\"hospitalIdList != null and hospitalIdList.size() != 0\">",
            "          and hospital_id in",
            "          <foreach collection=\"hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "      and finish_time &gt; #{beginDate}",
            "      and finish_time &lt; #{endDate}",
            "   </where>",
            "</script>"})
    List<SamplingRecord> listByCondition(@Param("inspectionId") Long inspectionId, @Param("hospitalIdList") List<Long> hospitalIdList, @Param("beginDate") Date beginDate, @Param("endDate") Date endDate);

    @Select({"<script>",
            "   select srr.* from sampling_record_result srr inner join sampling_record sr on sr.id=srr.sampling_id ",
            "   <where>",
            "      sr.deleted=0 and sr.check_type in (1,3) and sr.finish_time is not null",
            "      <if test=\"inspectionId!=null\">",
            "          and inspection_id=#{inspectionId}",
            "      </if> ",
            "      <if test=\"hospitalIdList != null and hospitalIdList.size() != 0\">",
            "          and sr.hospital_id in",
            "          <foreach collection=\"hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "      and sr.finish_time &gt; #{beginDate}",
            "      and sr.finish_time &lt; #{endDate}",
            "   </where>",
            "</script>"})
    List<SamplingRecordResult> listDiseaseResultByCondition(@Param("inspectionId") Long inspectionId, @Param("hospitalIdList") List<Long> hospitalIdList, @Param("beginDate") Date beginDate, @Param("endDate") Date endDate);

    @Select({"<script>",
            "   select srr.* from sampling_record_result srr inner join sampling_record sr on sr.id=srr.sampling_id ",
            "   <where>",
            "      sr.deleted=0 and type=2 and sr.check_type in (2,3) and sr.finish_time is not null",
            "      <if test=\"inspectionId!=null\">",
            "          and inspection_id=#{inspectionId}",
            "      </if> ",
            "      <if test=\"hospitalIdList != null and hospitalIdList.size() != 0\">",
            "          and sr.hospital_id in",
            "          <foreach collection=\"hospitalIdList\" item=\"item\" open=\"(\" separator=\",\" close=\")\"> ",
            "              #{item} ",
            "          </foreach> ",
            "      </if> ",
            "      and sr.finish_time &gt; #{beginDate}",
            "      and sr.finish_time &lt; #{endDate}",
            "   </where>",
            "</script>"})
    List<SamplingRecordResult> listDrugResultByCondition(@Param("inspectionId") Long inspectionId, @Param("hospitalIdList") List<Long> hospitalIdList, @Param("beginDate") Date beginDate, @Param("endDate") Date endDate);

    /**
     * 根据盒子编码查询盒子采样数据
     * @param boxCode
     * @return
     */
    List<SamplingRecord> listSamplingByBoxCode(String boxCode);
    /**
     * 根据盒子编码查询盒子采样数据
     * @param expressCode
     * @return
     */
    List<SamplingRecord> listSamplingByExpressCode(String expressCode);
}
